﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;


namespace LIS2012.DBUtility
{
    public partial class SQLHelper
    {
        /*---------------------------------------------- ExecuteSql ----------------------------------------------*/
        /// <summary>
        /// 对连接执行 Transact-SQL 语句并返回受影响的行数。
        /// </summary>
        /// <param name="cmdText"></param>
        /// <returns></returns>
        public static int ExecuteSql(string cmdText)
        {
            return SQLHelper.ExecuteSql(cmdText, null);
        }
        /// <summary>
        /// 对连接执行 Transact-SQL 语句并返回受影响的行数。
        /// </summary>
        /// <param name="cmdText"></param>
        /// <param name="cmdParms"></param>
        /// <returns></returns>
        public static int ExecuteSql(string cmdText, params SqlParameter[] cmdParms)
        {
            return SQLHelper.ExecuteSql(CommandType.Text, cmdText, cmdParms);
        }
        /// <summary>
        /// 对连接执行 Transact-SQL 语句并返回受影响的行数。
        /// </summary>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="cmdParms"></param>
        /// <returns></returns>
        public static int ExecuteSql(CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
        {
            PubConstant.PrepareCommand(cmdType, cmdText, cmdParms);
            return PubConstant.Command.ExecuteNonQuery();
        }
        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="sqlList"> SQL语句的哈希表(key为sql语句，value是该语句的SqlParameter[])</param>
        /// <returns></returns>
        public static int ExecuteSqlTran(System.Collections.Hashtable sqlList)
        {
            int rows = 0;
            using (SqlTransaction trans = PubConstant.Connection.BeginTransaction())
            {
                foreach (System.Collections.DictionaryEntry sql in sqlList)
                {
                    PubConstant.PrepareCommand(CommandType.Text, sql.Key.ToString(), (SqlParameter[])sql.Value);
                    try
                    {
                        if (PubConstant.Command.ExecuteNonQuery() > 0)
                        {
                            rows++;
                        }
                        trans.Commit();
                    }
                    catch (Exception ex)
                    {
                        trans.Rollback();
                        throw ex;
                    }
                }
            }
            return rows;
        }
        /*---------------------------------------------- GetSingle ----------------------------------------------*/
        /// <summary>
        /// 执行查询，并返回查询所返回的结果集中第一行的第一列。忽略其他列或行。
        /// </summary>
        /// <param name="cmdText"></param>
        /// <returns></returns>
        public static object GetSingle(string cmdText)
        {
            return SQLHelper.GetSingle(cmdText, null);
        }
        /// <summary>
        /// 执行查询，并返回查询所返回的结果集中第一行的第一列。忽略其他列或行。
        /// </summary>
        /// <param name="cmdText"></param>
        /// <param name="cmdParms"></param>
        /// <returns></returns>
        public static object GetSingle(string cmdText, params SqlParameter[] cmdParms)
        {
            return SQLHelper.GetSingle(cmdText, CommandType.Text, cmdParms);
        }
        /// <summary>
        /// 执行查询，并返回查询所返回的结果集中第一行的第一列。忽略其他列或行。
        /// </summary>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="cmdParms"></param>
        /// <returns></returns>
        public static object GetSingle(string cmdText, CommandType cmdType,params SqlParameter[] cmdParms)
        {
            PubConstant.PrepareCommand(cmdType, cmdText, cmdParms);
            return PubConstant.Command.ExecuteScalar();
        }
        /*---------------------------------------------- ExecuteReader ----------------------------------------------*/
        /// <summary>
        /// 提供一种从 SQL Server 数据库读取行的只进流的方式。
        /// </summary>
        /// <param name="cmdText"></param>
        /// <returns></returns>
        public static SqlDataReader ExecuteReader(string cmdText)
        {
            return SQLHelper.ExecuteReader(cmdText, null);
        }
        /// <summary>
        /// 提供一种从 SQL Server 数据库读取行的只进流的方式。
        /// </summary>
        /// <param name="cmdText"></param>
        /// <param name="cmdParms"></param>
        /// <returns></returns>
        public static SqlDataReader ExecuteReader(string cmdText, params SqlParameter[] cmdParms)
        {
            return SQLHelper.ExecuteReader(CommandType.Text, cmdText, cmdParms);
        }
        /// <summary>
        /// 提供一种从 SQL Server 数据库读取行的只进流的方式。
        /// </summary>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="cmdParms"></param>
        /// <returns></returns>
        public static SqlDataReader ExecuteReader(CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
        {
            PubConstant.PrepareCommand(cmdType, cmdText, cmdParms);
            return PubConstant.Command.ExecuteReader(CommandBehavior.Default);
        }
        /*---------------------------------------------- Query ----------------------------------------------*/
        /// <summary>
        /// 获取数据集
        /// </summary>
        /// <typeparam name="T"> DataSet、DataTable </typeparam>
        /// <param name="cmdText"></param>
        /// <returns></returns>
        public static T Query<T>(string cmdText)
        {
            return SQLHelper.Query<T>(cmdText, null);
        }
        /// <summary>
        /// 获取数据集
        /// </summary>
        /// <typeparam name="T"> DataSet、DataTable </typeparam>
        /// <param name="cmdText"></param>
        /// <param name="cmdParms"></param>
        /// <returns></returns>
        public static T Query<T>(string cmdText, params SqlParameter[] cmdParms)
        {
            return SQLHelper.Query<T>(CommandType.Text, cmdText, cmdParms);
        }
        /// <summary>
        /// 获取数据集
        /// </summary>
        /// <typeparam name="T"> DataSet、DataTable </typeparam>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="cmdParms"></param>
        /// <returns></returns>
        public static T Query<T>(CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
        {
            T collection = Activator.CreateInstance<T>();
            using (SqlDataAdapter adapter = new SqlDataAdapter(cmdText, PubConstant.ConnectionString))
            {
                adapter.SelectCommand.CommandType = cmdType;
                adapter.SelectCommand.Parameters.AddRange(cmdParms);
                if (typeof(T) == typeof(DataSet))
                {
                    adapter.Fill(collection as DataSet);
                }
                else if (typeof(T) == typeof(DataTable))
                {
                    adapter.Fill(collection as DataTable);
                }
                else
                {
                    collection = default(T);
                }
            }
            return collection;
        }
        /*---------------------------------------------- QueryPaging ----------------------------------------------*/
        /// <summary>
        /// 分页获取数据集
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="startRecord"></param>
        /// <param name="maxRecords"></param>
        /// <param name="srcTable"></param>
        /// <param name="cmdText"></param>
        /// <returns></returns>
        public static T QueryPaging<T>(int startRecord, int maxRecords, string srcTable, string cmdText)
        {
            return SQLHelper.QueryPaging<T>(startRecord, maxRecords, srcTable, cmdText, null);
        }
        /// <summary>
        /// 分页获取数据集
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="startRecord"></param>
        /// <param name="maxRecords"></param>
        /// <param name="srcTable"></param>
        /// <param name="cmdText"></param>
        /// <param name="cmdParms"></param>
        /// <returns></returns>
        public static T QueryPaging<T>(int startRecord, int maxRecords, string srcTable,string cmdText, params SqlParameter[] cmdParms)
        {
            return SQLHelper.QueryPaging<T>(startRecord, maxRecords, srcTable, CommandType.Text, cmdText, cmdParms);
        }
        /// <summary>
        /// 分页获取数据集
        /// </summary>
        /// <typeparam name="T"> DataSet、DataTable、DataTable[] </typeparam>
        /// <param name="startRecord"></param>
        /// <param name="maxRecords"></param>
        /// <param name="srcTable"></param>
        /// <param name="cmdText"></param>
        /// <param name="cmdParms"></param>
        /// <returns></returns>
        public static T QueryPaging<T>(int startRecord, int maxRecords, string srcTable,CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
        {
            T collection = Activator.CreateInstance<T>();
            using (SqlDataAdapter adapter = new SqlDataAdapter(cmdText, PubConstant.ConnectionString))
            {
                adapter.SelectCommand.CommandType = cmdType;
                adapter.SelectCommand.Parameters.AddRange(cmdParms);
                if (typeof(T) == typeof(DataSet))
                {
                    adapter.Fill(collection as DataSet, startRecord, maxRecords, srcTable);
                }
                else if (typeof(T) == typeof(DataTable))
                {
                    adapter.Fill(startRecord, maxRecords, collection as DataTable);
                }
                else if (typeof(T) == typeof(DataTable[]))
                {
                    adapter.Fill(startRecord, maxRecords, collection as DataTable[]);
                }
                else
                {
                    collection = default(T);
                }
            }
            return collection;
        }
        /*---------------------------------------------- ----------------------------------------------*/
    }
}
